#!/usr/bin/python
# -*- coding: iso-8859-15 -*-

from xml.etree.ElementTree import parse
from xml.etree.ElementTree import ElementTree
from xml.etree.ElementTree import Element
import re
#from xml.dom.minidom import parse
from DBA import DBAccess
from Mapping.mapManager import Mapping
from datetime import datetime	

class DBInterface:
	def __init__(self,filename="./DAC/DBA/dbaccess.config"):
		dom = parse(filename)
		root = dom.getroot()
		#DataBase connection
		self.dbname = root.find("dbname").text 				
		self.host   = root.find("host").text 				
		self.port   = int(root.find("port").text) 			
		self.user   = root.find("user").text 				
		self.passwd = root.find("passwd").text
		#Tables Names
		self.iwconfigtablename = root.find("tablesName").find("iwconfig").text
		self.iwlisttablename = root.find("tablesName").find("iwlist").text 
		self.celluleIwlisttablename = root.find("tablesName").find("celluleIwlist").text
		self.campagnetablename = root.find("tablesName").find("campagne").text 
		self.mesuretablename = root.find("tablesName").find("mesure").text
		self.accessPointtablename = root.find("tablesName").find("accessPoint").text 
		self.salletablename = root.find("tablesName").find("salle").text
		self.batimenttablename = root.find("tablesName").find("batiment").text 
		self.iperftablename = root.find("tablesName").find("iperf").text
		self.ref_AP_Salle_Campagnetablename = root.find("tablesName").find("ref_AP_Salle_Campagne").text 
		self.ref_iperf_mesuretablename = root.find("tablesName").find("ref_iperf_mesure").text
		#Mapping FileName
		self.iwlistmapping = root.find("mappingFiles").find("iwlist").text
		self.iwconfigmapping = root.find("mappingFiles").find("iwconfig").text
		self.celluleIwlistmapping = root.find("mappingFiles").find("celluleIwlist").text
		self.campagnemapping = root.find("mappingFiles").find("campagne").text
		self.mesuremapping = root.find("mappingFiles").find("mesure").text
		self.accessPointmapping = root.find("mappingFiles").find("accessPoint").text
		self.sallemapping = root.find("mappingFiles").find("salle").text
		self.iperfmapping = root.find("mappingFiles").find("iperf").text
		self.batimentmapping = root.find("mappingFiles").find("batiment").text
		self.ref_iperf_mesuremapping = root.find("mappingFiles").find("iperfMesure").text
		self.ref_AP_Salle_CampagneMapping = root.find("mappingFiles").find("ref_AP_Salle_Campagne").text
	
	def __createDBA(self):
		return DBAccess(dbname=self.dbname, host=self.host, port=self.port, user =self.user,passwd=self.passwd)
	
	def __createTuple(self,dico,mapping):
		keys = [mapping.getValue(x) for x in dico.keys()]
		values = dico.values()
		i = 0;
		d = {};
		for elem in keys:
			d[elem] = values[i]
			i=i+1
		return d

	def __createTupleInverse(self,dicoInverse,mapping):
		keys = [mapping.getKey(x) for x in dicoInverse.keys()]
		values = dicoInverse.values()
		i = 0;
		d = {};
		for elem in keys:
			d[elem] = values[i]
			i=i+1
		return d

	def createNewID(self):
		uid = ''
		date = datetime.today()
		uid = uid+str(date.year)+"-"+str(date.month)+"-"+str(date.day)+"-"+str(date.hour)+"-"+str(date.minute)+"-"+str(date.second)+"-"+str(date.microsecond)
		return uid;
	
	def insert(self,tableName,tablemapping,dicodata):
		dba = self.__createDBA()
		mapping = Mapping(tablemapping)
		dba.insert(tableName,self.__createTuple(dicodata,mapping))

	def update(self,tableName,tablemapping,dicodata):
		dba = self.__createDBA()
		mapping = Mapping(tablemapping)
		dba.update(tableName,self.__createTuple(dicodata,mapping))

	def selectTable(self,tablename,tablemapping,clause={}):
		dba = self.__createDBA()
		mapping = Mapping(tablemapping)
		query = "select * from "+str(tablename)+self.__getClause(clause,mapping)
		result = dba.executeQuery(query).dictresult()
		table = []
		for row in result:
			table.append(self.__createTupleInverse(row,mapping))
		return table;

	def selectTableInverse(self,tablename,clause={}):
		dba = self.__createDBA()
		query = "select * from "+str(tablename)+self.__getClauseInverse(clause)
		result = dba.executeQuery(query).dictresult()
		table = []
		for row in result:
			table.append(row)
		return table;

	def __getClause(self,dico,mapping):
		clause = ''
		if dico:
			clause = clause + " Where "
			filtre = self.__createTuple(dico,mapping)
			has_new = 0;
			for key in filtre.keys():
				if has_new:
					clause = clause+" and "
				clause = clause+str(key)+"= '" + str(filtre[key])+"'"
				has_new = has_new+1;
		return clause

	def __getClauseInverse(self,dico):
		clause = ""
		if dico:
			clause = clause+" Where "
			has_new = 0;
			for key in dico.keys():
				if has_new:
					clause = clause+" and "
				clause = clause+str(key)+"= '" + str(dico[key])+"'"
				has_new = has_new+1;
		return clause

	def exportDataToFile(self,filename):
		
		dba = self.__createDBA()
		NomTables = dba.getTable()
		root = Element("Tables")
		expr = re.compile("information_schema.")
		print NomTables 
		for nom in NomTables:
			if not expr.findall(nom):
				T = Element("Table")
				ElementNom = Element("nom")
				ElementNom.text = nom;
				T.append(ElementNom);
				attributes = dba.getAttNameForTable(nom)
				TableData = self.selectTableInverse(nom)
				ElementAtt = Element("Donnees")
				for row in TableData:
					ElementRow = Element("Row")
					for attribut in attributes:
				    
						colonne = Element(attribut)
						colonne.text = str(row[attribut])
						ElementRow.append(colonne)
					ElementAtt.append(ElementRow)
				T.append(ElementAtt)
				root.append(T)
		Xmltree = ElementTree(root)
		Xmltree.write(filename)

if __name__=="__main__":
	db = DBInterface("./dbaccess.config")
	db.exportDataToFile("export.xml")
